Flight - Homework2 - Ngoc131112(Python)

# File location and type
file_location = "/FileStore/tables/airlines.csv"
file_type = "csv"

# CSV options
infer_schema = "true"
first_row_is_header = "true"
delimiter = ","

# The applied options are for CSV files. For other file types, these will be ignored.
df_airlines = spark.read.format(file_type) \
  .option("inferSchema", infer_schema) \
  .option("header", first_row_is_header) \
  .option("sep", delimiter) \
  .load(file_location)

temp_table_name = "airlines"
df_airlines.createOrReplaceTempView(temp_table_name)
display(df_airlines)

%sql
--Find out which airports when treated as Origin have the smallest and the largest Departure delays.
select Origin, MIN(DepDelay) as MinDepartureDelay, MAX(DepDelay) as MaxDepartureDelay
from flights
group by Origin
order by MinDepartureDelay asc, MaxDepartureDelay desc

%sql
--Create a list of U.S. States with the number of airports that each of them has in descending order. 
--Show this on a map of the U.S. (Hint: Use: a choropleth map. States are listed as USPS Abbreviations)

select State, count(*) as AirportCount
from airports
where State is not NULL
group by State
order by AirportCount desc

%sql
-- Create a list containing: DATE, NUM_OF_FLIGHTS, MAX_ARRIVAL_DELAY, AVG_ARRIVAL_DELAY. 
-- Please provide the date (from FlightDate) in the following format i.e. 2018-01-10. 
-- The maximum and Average delays should be provided in minutes but rounded to 2 digits after the decimal point.

select 
    to_date(FlightDate) as DATE,
    count(*) as NUM_OF_FLIGHTS,
    round(max(ArrDelay), 2) as MAX_ARRIVAL_DELAY,
    round(avg(ArrDelay), 2) as AVG_ARRIVAL_DELAY
from flights
group by to_date(FlightDate)

%sql
-- Show on a bar chart the number of flights per Carrier. Use the full name of the airline (from airlines.csv).

select a.airline, count(*) as flightcount
from flights f
join airlines a on f.Carrier = a.IATA_CODE
group by airline
order by flightcount